﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbAdapter.sqldbAdapter;
using System.Data;
using System.Data.SqlClient;

namespace DAO.WaiGaoQiao
{
    /// <summary>
    /// 劳保物品入库
    /// </summary>
    public class DataAccessWork_Storage
    {
        /// <summary>
        /// 保存劳保物品入库
        /// </summary>
        /// <param name="idpara"></param>
        /// <returns></returns>
        public int SaveStorageDetailed(object[] idpara)
        {
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(GetConntionString.conntionstring(), "Proc_WorkStorageDetailed", idpara);
        }

        /// <summary>
        /// 获取劳保物品明细总数
        /// </summary>
        /// <returns></returns>
        public int GetCountStorageDetailed(string condition)
        {
            string query = "select count(1) from Work_Storage_Detailed where 1=1";
            if (!string.IsNullOrEmpty(condition))
            {
                query += condition;
            }
            sqldb db = new sqldb();
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }
        /// <summary>
        /// 根据条件获取劳保物品（不分页）
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataSet GetAllStorageDetailed(string condition)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select * FROM Work_Storage_Detailed Where 1=1 " + condition;
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }
        /// <summary>
        /// 获取劳保物品分页
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="pagesize"></param>
        /// <param name="startindex"></param>
        /// <returns></returns>
        public DataSet GetAllStorageDetailed(string condition, int pagesize, int startindex)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                int startrow = pagesize * (startindex - 1);
                int endrow = pagesize * startindex + 1;
                string query = "SELECT B.ID,B.StorageDt,B.Article_ID,B.Article_NO,"
                    + "B.Article_Unit,B.Article_Color,B.Supplier,B.Count,B.OperatorID,B.Remark,B.RecordDateTime,B.OrderNo "
                    + "FROM (select ROW_NUMBER() OVER(ORDER BY A.ID DESC) rownumber,ID ,StorageDt,"
                    + " Article_ID,Article_NO,Article_Unit,Article_Color ,Supplier,Count ,OperatorID,Remark,RecordDateTime,OrderNo"
	                + " from Work_Storage_Detailed A Where 1=1 " + condition + ") B "
                    + " where B.rownumber > " + startrow.ToString() + " and B.rownumber < " + endrow.ToString();
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }
        /// <summary>
        /// 根据条件删除劳保物品
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int DeleteStorageDetailed(string condition)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "Delete from Work_Storage_Detailed where " + condition;
                return db.ExecuteNonQuery(conn, CommandType.Text, query);
            }
        }

        /// <summary>
        /// 根据条件删除(用来判断是否存在出库的情况，存在出库，返回0，反之删除)
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int DeleteStorageDetailedWC(string condition, int id)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
                {
                    /*
                     *先根据主副调食材入库编号查询出食材类型编号和单价，然后再去主副调食材出库表里面查询一下有没有这个食材类型
                     *编号和这个单价的出库信息，如果有则不能删除直接返回0，如果没有则可以删除，返回删除影响行数。
                     */
                    int i;
                    sqldb db = new sqldb();
                    string query = "select [Article_ID] from Work_Storage_Detailed where ID='" + id + "'";
                    DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                    query = "select count(*) from Work_OutStock_Detailed where Article_ID='" + ds.Tables[0].Rows[0]["Article_ID"].ToString() + "'";
                    ds = db.ExecuteDataset(conn, CommandType.Text, query);
                    if (ds.Tables[0].Rows[0][0].ToString() == "0")
                    {
                        query = "Delete from Work_Storage_Detailed where " + condition;
                        i = db.ExecuteNonQuery(conn, CommandType.Text, query);
                    }
                    else
                    {
                        i = 0;
                    }
                    return i;
                }
            }
            catch (Exception e)
            {
               
                Console.Write(e.Message);
                return 0;
            }
        }

        /// <summary>
        /// 获取劳保物品批号
        /// </summary>
        /// <returns></returns>
        public string GetStorageDetailedOrderNo()
        {
            sqldb db = new sqldb();
            string query = "Execute GetWork_Storage_Detailed_OrderNo";
            object strorderno = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return strorderno.ToString();
        }
    }
}
